/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sbmart.smartserver.task;

import com.mchange.v2.c3p0.C3P0Registry;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.concurrent.ExecutorService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sbmart.object.*;
import sbmart.smartserver.cache.ConnectionManager;
import sbmart.smartserver.job.ClientTrsJob;
import sbmart.smartserver.service.ClientMstService;
import sbmart.smartserver.service.ClientProcedures;
import sbmart.smartserver.service.ServerProcedures;
import sbmart.smartserver.service.ServerTrsSalesService;
import sbmart.smartserver.service.ServerTrsService;
import sbmart.util.Ping;

/**
 *
 * @author albert
 *
 */
public class ClientTrsTask implements Runnable {

    private List<Header> headers = new ArrayList<Header>();
    public static final Logger LOG = LoggerFactory.getLogger("ClientTrs");
    private ExecutorService exec;

    public ClientTrsTask(List<Header> headers, ExecutorService exec) {
        this.headers = headers;
        this.exec = exec;
    }

    private Connection getClientConn(Header header) throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = ConnectionManager.instance().getClientConnection(header);
        return conn;
    }

    private void closeClientConn(Header header, Connection conn) throws SQLException {
        LOG.debug("[CALL]");
        ConnectionManager.instance().closeClientConnection(header, conn);
    }

    private Connection getServerConn() throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = C3P0Registry.pooledDataSourceByName("sbmart2").getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    private void rollback(Header header, Connection serverConn, Connection clientConn, SQLException ex) {
        LOG.error("<ERROR> " + header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress(), ex);
        try {
            if (clientConn != null) {
                clientConn.rollback();
            }
            if (serverConn != null) {
                serverConn.rollback();
            }
            LOG.debug("[ROLLBACK] ", ex);
        } catch (SQLException ex1) {
            LOG.error("[ERROR]", ex1);
        } finally {
            return;
        }
    }

    @Override
    public void run() {
        try {
            LOG.info("<RUN> " + getClass().getSimpleName());
            Connection clientConn = null, serverConn = null;

            serverConn = getServerConn();

            for (Header header : headers) {
                boolean isContinue = false;
                boolean isSend = false;
                LOG.debug(header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress());
                ClientTrsJob.count = 0;
                try {
                    if (Ping.isReachable(header.getIpaddress())) {
                        clientConn = getClientConn(header);
                        if (clientConn == null || clientConn.isClosed()) {
                            LOG.debug("<PASS> InUsed:" + header.getBranchName() + "|" + header.getIpaddress());
                            isContinue = true;
                        }

                        if (!isContinue) {
                            putTrsSend(header, clientConn);
                            isSend = isTrsSend(header, clientConn);

                            if (isSend) {
                                if (serverConn.isClosed()) {
                                    serverConn = getServerConn();
                                }

                                if (!serverConn.isClosed() && !clientConn.isClosed()) {
                                    //getTrsSendGerai(header, clientConn, serverConn);
                                    getTrsBkl(header, clientConn, serverConn);
                                    getTrsOrderIntern(header, clientConn, serverConn);
                                    getTrsReturBkl(header, clientConn, serverConn);
                                    getTrsReturGerai(header, clientConn, serverConn);
                                    getTrsReturOther(header, clientConn, serverConn);
                                    getUsedVoucherList(header, clientConn, serverConn);
                                    getTrsAdjustGerai(header, clientConn, serverConn);
                                    getTrsSalesVoucher(header, clientConn, serverConn);
                                    getTrsSalesOnline(header, clientConn, serverConn);
                                    getTrsRentPayment(header, clientConn, serverConn);
                                    getTrsSalesRemittance(header, clientConn, serverConn);

                                    getTrsMitraPaymentList(header, clientConn, serverConn);
                                    getMstItemRequest(header, clientConn, serverConn);
                                    //getMstPkmGerai(header, clientConn, serverConn);

                                    getTrAbsensi(header, clientConn, serverConn);
                                    getTrRequest(header, clientConn, serverConn);

                                } else {
                                    LOG.error("[ERROR] connection refuse or close");
                                }
                            }
                        }
                    } else {
                        LOG.error("[ERROR] PING " + header.getBranchName() + "|" + header.getIpaddress());
                    }
                } catch (SQLException ex) {
                    LOG.error("[ERROR] " + header.getBranchName() + "|" + header.getIpaddress() + " : " + ex.getMessage(), ex);
                    try {
                        if (clientConn != null && !clientConn.isClosed()) {
                            clientConn.rollback();
                        }

                        if (serverConn != null && !serverConn.isClosed()) {
                            serverConn.rollback();
                        }
                    } catch (SQLException ex1) {
                        LOG.error("[ERROR] Rollback:" + header.getBranchName() + "|" + header.getIpaddress(), ex1);
                    }
                } catch (Exception ex) {
                    LOG.error("[ERROR] " + header.getBranchName() + "|" + header.getIpaddress(), ex);
                } finally {
                    try {
                        if (serverConn != null) {
                            serverConn.close();
                        }
                        closeClientConn(header, clientConn);



                    } catch (SQLException ex) {
                        LOG.error("[ERROR] " + header.getBranchName() + "|" + header.getIpaddress(), ex);
                    }
                }

            }
            LOG.info("<END> " + getClass().getSimpleName());
        } catch (SQLException ex) {
            LOG.error("[ERROR]" + " : " + ex.getMessage());
        }
    }

    private boolean isTrsSend(Header header, Connection clientConn) throws SQLException {
//        LOG.debug("[CALL]"); 

        boolean isExists = true;
        CallableStatement call_is_sendhistory_nonsales_exists = clientConn.prepareCall(ClientProcedures.is_sendhistory_nonsales_exists);
        call_is_sendhistory_nonsales_exists.registerOutParameter(1, Types.INTEGER);

        ConnectionManager.instance().isOpen(clientConn);
        call_is_sendhistory_nonsales_exists.execute();
        int out = call_is_sendhistory_nonsales_exists.getInt(1);
        if (out == 0) {
            isExists = false;
            LOG.debug("<FALSE>");
        } else {
            isExists = true;
            LOG.debug("<TRUE>");
        }
        clientConn.commit();
        return isExists;
    }

    private void putTrsSend(Header header, Connection clientConn) throws SQLException {
//        LOG.debug("[CALL]");
        CallableStatement call_put_trs_sendhistory = clientConn.prepareCall(ClientProcedures.put_trs_sendhistory);

        ConnectionManager.instance().isOpen(clientConn);
        call_put_trs_sendhistory.execute();
        clientConn.commit();
    }

    private void getTrsBkl(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_bkl_mst = clientConn.prepareCall(ClientProcedures.get_trs_bkl_mst);
            CallableStatement call_get_trs_bkl_detail = clientConn.prepareCall(ClientProcedures.get_trs_bkl_detail);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_bkl_mst.executeQuery();
            List<TrsBklMst> list = new ArrayList<TrsBklMst>();
            while (rs.next()) {
                TrsBklMst mst = new TrsBklMst();
                int transID = rs.getInt("bkl_id");
                mst.setBklId(transID);
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setTransNum(rs.getString("trans_num"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                mst.setTransDate(cal1);

                mst.setJenisPajak(rs.getByte("jenis_pajak"));
                mst.setRelationId(rs.getShort("relation_id"));
                mst.setNoFaktur(rs.getString("no_faktur"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setDiscount(rs.getFloat("discount"));
                mst.setTax(rs.getFloat("tax"));
                mst.setTotal(rs.getFloat("total"));
                mst.setDivisi(rs.getString("divisi"));
                mst.setDiscGo(rs.getByte("disc_go"));
                mst.setNotes(rs.getString("notes"));
                mst.setTopBuying(rs.getByte("top_buying"));
                Calendar cal2 = null;
                if (rs.getDate("faktur_date") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("faktur_date").getTime());
                }
                mst.setFakturDate(cal2);

                List<TrsBklDetail> details = new ArrayList<TrsBklDetail>();
                call_get_trs_bkl_detail.setInt(1, transID);
                ResultSet rs2 = call_get_trs_bkl_detail.executeQuery();
                while (rs2.next()) {
                    TrsBklDetail detail = new TrsBklDetail();
                    detail.setItemId(rs2.getInt("item_id"));
                    detail.setBranchId(rs2.getInt("branch_id"));
                    detail.setQuantity2(rs2.getFloat("quantity2"));
                    detail.setQtyBonus(rs2.getFloat("qty_bonus"));
                    detail.setPrice(rs2.getFloat("price"));
                    detail.setDisc(rs2.getFloat("disc"));
                    detail.setTax(rs2.getFloat("tax"));
                    detail.setAmount(rs2.getFloat("amount"));
                    detail.setFracQty(rs2.getFloat("frac_qty"));
                    detail.setFracPrice(rs2.getFloat("frac_price"));
                    detail.setFracBonus(rs2.getFloat("frac_bonus"));
                    detail.setItemBonusId(rs2.getInt("item_bonus_id"));
                    detail.setDiscValuePcs(rs2.getFloat("disc_value_pcs"));
                    detail.setHppId(rs2.getInt("hpp_id"));
                    detail.setJenisBpb(rs2.getByte("jenis_bpb"));
                    detail.setDiscExcludeOi(rs2.getFloat("disc_exclude_oi"));
                    detail.setDiscGo(rs2.getFloat("disc_go"));
                    detail.setNotes(rs2.getString("notes"));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putTrsBkl(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }

    }

    private void getTrsOrderIntern(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_order_intern_mst = clientConn.prepareCall(ClientProcedures.get_trs_order_intern_mst);
            CallableStatement call_get_trs_order_intern_detail = clientConn.prepareCall(ClientProcedures.get_trs_order_intern_detail);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_order_intern_mst.executeQuery();
            List<TrsPbGeraiMst> list = new ArrayList<TrsPbGeraiMst>();
            while (rs.next()) {
                TrsPbGeraiMst mst = new TrsPbGeraiMst();
                int transID = rs.getInt("pb_id");
                mst.setPbId(transID);
                mst.setTransNum(rs.getString("trans_num"));
                Calendar cal1 = Calendar.getInstance();
                cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                mst.setTransDate(cal1);
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setUsersId(rs.getString("users_id"));
                mst.setNotes(rs.getString("notes"));
                mst.setPbType(rs.getByte("pb_type"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setTax(rs.getFloat("tax"));
                mst.setTotal(rs.getFloat("total"));
                mst.setInsertLog(rs.getString("insert_log"));


                List<TrsPbGeraiDetail> details = new ArrayList<TrsPbGeraiDetail>();
                call_get_trs_order_intern_detail.setInt(1, transID);

                ConnectionManager.instance().isOpen(clientConn);
                ResultSet rs2 = call_get_trs_order_intern_detail.executeQuery();
                while (rs2.next()) {
                    TrsPbGeraiDetail detail = new TrsPbGeraiDetail();
                    detail.setItemId(rs2.getInt("item_id"));
                    detail.setQuantity(rs2.getFloat("quantity"));
                    detail.setPrice(rs2.getFloat("price"));
                    detail.setTax(rs2.getFloat("tax"));
                    detail.setAmount(rs2.getFloat("amount"));
                    detail.setIsTax(rs2.getByte("is_tax"));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrsOrderIntern(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsReturBkl(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_retur_bkl_mst = clientConn.prepareCall(ClientProcedures.get_trs_retur_bkl_mst);
            CallableStatement call_get_trs_retur_bkl_detail = clientConn.prepareCall(ClientProcedures.get_trs_retur_bkl_detail);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_retur_bkl_mst.executeQuery();
            List<TrsReturBklMst> list = new ArrayList<TrsReturBklMst>();
            while (rs.next()) {
                TrsReturBklMst mst = new TrsReturBklMst();
                mst.setReturBklId(rs.getInt("retur_bkl_id"));
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setTransNum(rs.getString("trans_num"));
                Calendar cal1 = Calendar.getInstance();
                cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                mst.setTransDate(cal1);
                mst.setRelationId(rs.getShort("relation_id"));
                mst.setTotal(rs.getFloat("total"));
                mst.setNotes(rs.getString("notes"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setTax(rs.getFloat("tax"));
                mst.setDiscount(rs.getFloat("discount"));


                List<TrsReturBklDetail> details = new ArrayList<TrsReturBklDetail>();
                call_get_trs_retur_bkl_detail.setInt(1, mst.getReturBklId());

                ConnectionManager.instance().isOpen(clientConn);
                ResultSet rs2 = call_get_trs_retur_bkl_detail.executeQuery();
                while (rs2.next()) {
                    TrsReturBklDetail detail = new TrsReturBklDetail();
                    detail.setItemId(rs2.getInt("item_id"));
                    detail.setQuantity(rs2.getFloat("quantity"));
                    detail.setPrice(rs2.getFloat("price"));
                    detail.setAmount(rs2.getFloat("amount"));
                    detail.setTax(rs2.getFloat("tax"));
                    detail.setDiscount(rs2.getFloat("discount"));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrsReturBkl(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsReturGerai(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_retur_gerai_mst = clientConn.prepareCall(ClientProcedures.get_trs_retur_gerai_mst);
            CallableStatement call_get_trs_retur_gerai_detail = clientConn.prepareCall(ClientProcedures.get_trs_retur_gerai_detail);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_retur_gerai_mst.executeQuery();
            List<TrsReturGeraiMst> list = new ArrayList<TrsReturGeraiMst>();
            while (rs.next()) {
                TrsReturGeraiMst mst = new TrsReturGeraiMst();
                int returId = rs.getInt("retur_gr_id");
                mst.setReturGrId(returId);
                mst.setTransNum(rs.getString("trans_num"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                mst.setTransDate(cal1);

                mst.setBranchId(rs.getShort("branch_id"));
                mst.setNotes(rs.getString("notes"));
                mst.setReturType(rs.getByte("retur_type"));
                mst.setUsersId(rs.getString("users_id"));
                mst.setTax(rs.getFloat("tax"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setTotal(rs.getFloat("total"));

                List<TrsReturGeraiDetail> details = new ArrayList<TrsReturGeraiDetail>();

                call_get_trs_retur_gerai_detail.setInt("retur_id", returId);

                ConnectionManager.instance().isOpen(clientConn);
                ResultSet rs2 = call_get_trs_retur_gerai_detail.executeQuery();
                while (rs2.next()) {
                    TrsReturGeraiDetail detail = new TrsReturGeraiDetail();
                    detail.setItemId(rs2.getInt("item_id"));
                    detail.setQuantity(rs2.getFloat("quantity"));
                    detail.setPrice(rs2.getFloat("price"));
                    detail.setAmount(rs2.getFloat("amount"));
                    detail.setRusakId(rs2.getByte("rusak_id"));
                    detail.setTax(rs2.getFloat("tax"));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putTrsReturGerai(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsReturOther(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_retur_other_mst = clientConn.prepareCall(ClientProcedures.get_trs_retur_other_mst);
            CallableStatement call_get_trs_retur_other_detail = clientConn.prepareCall(ClientProcedures.get_trs_retur_other_detail);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_retur_other_mst.executeQuery();
            List<TrsReturOtherMst> list = new ArrayList<TrsReturOtherMst>();
            while (rs.next()) {
                TrsReturOtherMst mst = new TrsReturOtherMst();
                mst.setReturOtherId(rs.getInt("retur_other_id"));
                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                mst.setTransDate(cal1);
                mst.setTransNum(rs.getString("trans_num"));
                mst.setBranchId(rs.getShort("branch_id"));
                mst.setGudangId(rs.getShort("gudang_id"));
                mst.setReturType(rs.getShort("retur_type"));
                mst.setNotes(rs.getString("notes"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setTotal(rs.getFloat("total"));


                List<TrsReturOtherDetail> details = new ArrayList<TrsReturOtherDetail>();
                call_get_trs_retur_other_detail.setInt(1, mst.getReturOtherId());

                ConnectionManager.instance().isOpen(clientConn);
                ResultSet rs2 = call_get_trs_retur_other_detail.executeQuery();
                while (rs2.next()) {
                    TrsReturOtherDetail detail = new TrsReturOtherDetail();
                    detail.setItemId(rs2.getInt("item_id"));
                    detail.setQuantity(rs2.getFloat("quantity"));
                    detail.setPrice(rs2.getFloat("price"));
                    detail.setAmount(rs2.getFloat("amount"));
                    detail.setTax(rs2.getFloat("tax"));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrsReturOther(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsSalesVoucher(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_sales_voucher = clientConn.prepareCall(ClientProcedures.get_trs_sales_voucher);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_sales_voucher.executeQuery();
            List<TrsSalesVoucher> list = new ArrayList<TrsSalesVoucher>();
            while (rs.next()) {
                TrsSalesVoucher trs = new TrsSalesVoucher();
                trs.setBranchId(rs.getInt("branch_id"));
                trs.setVoucherId(rs.getInt("voucher_id"));
                trs.setSalesId(rs.getInt("sales_id"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTime(rs.getDate("trans_date"));
                }
                trs.setTransDate(cal1);

                trs.setAmount(rs.getFloat("amount"));
                trs.setVoucherCode(rs.getString("voucher_code"));
                trs.setInsertLog(rs.getString("insert_log"));
                trs.setEditLog(rs.getString("edit_log"));
                trs.setJenis(rs.getByte("jenis"));
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrsSalesVoucher(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }

    }

    private void getUsedVoucherList(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_used_voucher_list = clientConn.prepareCall(ClientProcedures.get_used_voucher_list);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_used_voucher_list.executeQuery();
            List<MstVoucher> list = new ArrayList<MstVoucher>();
            while (rs.next()) {
                MstVoucher trs = new MstVoucher();
                trs.setVoucherId(rs.getInt("voucher_id"));
                trs.setBranchId((short) header.getBranchId());
                Calendar cal1 = null;
                if (rs.getDate("used_date") != null) {
                    cal1.setTimeInMillis(rs.getDate("used_date").getTime());
                }
                trs.setUsedDate(cal1);
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putUsedVoucherList(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }

    }

    private void getTrsAdjustGerai(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_adjust_gerai_mst = clientConn.prepareCall(ClientProcedures.get_trs_adjust_gerai_mst);
            CallableStatement call_get_trs_adjust_gerai_detail = clientConn.prepareCall(ClientProcedures.get_trs_adjust_gerai_detail);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_adjust_gerai_mst.executeQuery();
            List<TrsAdjustGeraiMst> list = new ArrayList<TrsAdjustGeraiMst>();
            while (rs.next()) {
                TrsAdjustGeraiMst trs = new TrsAdjustGeraiMst();
                int adj_gerai_id = rs.getInt("adj_gerai_id");
                trs.setAdjGeraiId(adj_gerai_id);
                trs.setTransNum(rs.getString("trans_num"));
                trs.setJenis(rs.getByte("jenis"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                trs.setTransDate(cal1);
                trs.setBranchId(rs.getShort("branch_id"));
                trs.setNotes(rs.getString("notes"));
                trs.setUsersId(rs.getString("users_id"));
                trs.setStatusId(rs.getByte("status_id"));
                trs.setAdjType(rs.getByte("adj_type"));
                trs.setInsertLog(rs.getString("insert_log"));
                trs.setEditLog(rs.getString("edit_log"));
                trs.setAdjCount(rs.getInt("adj_count"));

                List<TrsAdjustGeraiDetail> details = new ArrayList<TrsAdjustGeraiDetail>();
                call_get_trs_adjust_gerai_detail.setInt(1, adj_gerai_id);

                ConnectionManager.instance().isOpen(clientConn);
                ResultSet rs2 = call_get_trs_adjust_gerai_detail.executeQuery();
                while (rs2.next()) {
                    TrsAdjustGeraiDetail detail = new TrsAdjustGeraiDetail();
                    detail.setItemId(rs2.getInt("item_id"));
                    detail.setConvId(rs2.getInt("conv_id"));
                    detail.setQuantity(rs2.getFloat("quantity"));
                    detail.setStokSystem(rs2.getFloat("stok_system"));
                    detail.setConversion(rs2.getInt("conversion"));
                    detail.setAlasanId(rs2.getShort("alasan_id"));
                    detail.setPrice(rs2.getFloat("price"));
                    detail.setStokValue(rs2.getFloat("stok_value"));
                    detail.setRangeValue(rs2.getFloat("range_value"));
                    details.add(detail);
                }
                trs.setDetails(details);
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putTrsAdjustGerai(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsSendGerai(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement call_get_trs_sendhistory = clientConn.prepareCall(ClientProcedures.get_trs_sendhistory);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_sendhistory.executeQuery();
            List<TrsSendhistory> list = new ArrayList<TrsSendhistory>();
            while (rs.next()) {
                TrsSendhistory trs = new TrsSendhistory();
                trs.setBranchId(rs.getShort("branch_id"));
                trs.setTransId(rs.getInt("trans_id"));
                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                trs.setTransDate(cal1);
                trs.setTransType(rs.getString("trans_type"));
                trs.setTransStatus(rs.getShort("trans_status"));
                list.add(trs);
            }

            if (!list.isEmpty()) {
                //LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrsSendHistoryGerai(header, list, serverConn);
                serverConn.commit();
                //LOG.debug("<COMMIT>");
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsSalesOnline(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_sales_online = clientConn.prepareCall(ClientProcedures.get_trs_sales_online);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_sales_online.executeQuery();
            List<TrsSalesOnline> list = new ArrayList<TrsSalesOnline>();
            while (rs.next()) {
                TrsSalesOnline trs = new TrsSalesOnline();
                trs.setSalesOnlineId(rs.getInt("sales_online_id"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                trs.setTransDate(cal1);

                trs.setBranchId(rs.getShort("branch_id"));
                trs.setRekapId(rs.getInt("rekap_id"));
                trs.setTransType(rs.getInt("trans_type"));
                trs.setIdPelanggan(rs.getString("id_pelanggan"));
                trs.setNama(rs.getString("nama"));
                trs.setQuantity(rs.getFloat("quantity"));
                trs.setTagihan(rs.getFloat("tagihan"));
                trs.setAdm(rs.getFloat("adm"));
                trs.setMaterai(rs.getFloat("materai"));
                trs.setPpn(rs.getFloat("ppn"));
                trs.setPpj(rs.getFloat("ppj"));
                trs.setMediaBayar(rs.getShort("media_bayar"));
                trs.setCash(rs.getFloat("cash"));
                trs.setCardId(rs.getShort("card_id"));
                trs.setCard(rs.getFloat("card"));
                trs.setSumbangan(rs.getFloat("sumbangan"));
                trs.setVoucher(rs.getFloat("voucher"));
                trs.setTotal(rs.getFloat("total"));
                trs.setCashBack(rs.getFloat("cash_back"));
                trs.setRespon(rs.getString("respon"));
                trs.setFinsert(rs.getShort("finsert"));
                trs.setInqId(rs.getLong("inq_id"));

                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putTrsSalesOnline(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsRentPayment(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_rent_payment = clientConn.prepareCall(ClientProcedures.get_trs_rent_payment);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_rent_payment.executeQuery();
            List<TrsRentPayment> list = new ArrayList<TrsRentPayment>();

            while (rs.next()) {
                TrsRentPayment trs = new TrsRentPayment();
                trs.setBranchId(rs.getInt("branch_id"));
                trs.setPayId(rs.getInt("pay_id"));
                trs.setRekapId(rs.getInt("rekap_id"));
                trs.setRelationId(rs.getShort("relation_id"));
                trs.setAmount(rs.getFloat("amount"));
                trs.setInsertLog(rs.getString("insert_log"));
                trs.setEditLog(rs.getString("edit_log"));
                trs.setRentId(rs.getInt("rent_id"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                trs.setTransDate(cal1);

                Calendar cal2 = null;
                if (rs.getDate("start_date") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("start_date").getTime());
                }
                trs.setStartDate(cal2);

                Calendar cal3 = null;
                if (rs.getDate("end_date") != null) {
                    cal3 = Calendar.getInstance();
                    cal3.setTimeInMillis(rs.getDate("end_date").getTime());
                }
                trs.setEndDate(cal3);

                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsSalesService.instance().putTrsRentPayment(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsSalesRemittance(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_trs_sales_remittance = clientConn.prepareCall(ClientProcedures.get_trs_sales_remittance);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_sales_remittance.executeQuery();
            List<TrsSalesRemittance> list = new ArrayList<TrsSalesRemittance>();

            while (rs.next()) {
                TrsSalesRemittance trs = new TrsSalesRemittance();
                trs.setSalesRemittanceId(rs.getInt("sales_remittance_id"));
                trs.setTransCode(rs.getString("trans_code"));
                trs.setTransType(rs.getShort("trans_type"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
                }
                trs.setTransDate(cal1);

                trs.setSenderId(rs.getString("sender_id"));
                trs.setSenderName(rs.getString("sender_name"));
                trs.setSenderAddress(rs.getString("sender_address"));
                trs.setSenderCity(rs.getString("sender_city"));
                trs.setSenderPhone(rs.getString("sender_phone"));
                trs.setReceiverId(rs.getString("receiver_id"));
                trs.setReceiverName(rs.getString("receiver_name"));
                trs.setReceiverAddress(rs.getString("receiver_address"));
                trs.setReceiverCity(rs.getString("receiver_city"));
                trs.setReceiverPhone(rs.getString("receiver_phone"));
                trs.setTransNominal(rs.getFloat("trans_nominal"));
                trs.setTransCost(rs.getFloat("trans_cost"));
                trs.setTransTotal(rs.getFloat("trans_total"));
                trs.setTransFee(rs.getFloat("trans_fee"));
                trs.setTransSave(rs.getFloat("trans_save"));
                trs.setTransReff(rs.getString("trans_reff"));
                trs.setIsRefund(rs.getShort("is_refund"));
                trs.setBranchId(rs.getInt("branch_id"));
                trs.setRekapId(rs.getInt("rekap_id"));
                
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsSalesService.instance().putTrsSalesRemittance(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrAbsensi(Header header, Connection clientConn, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_tr_absensi = clientConn.prepareCall(ClientProcedures.get_tr_absensi);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_tr_absensi.executeQuery();
            List<TrAbsensi> list = new ArrayList<TrAbsensi>();

            while (rs.next()) {
                TrAbsensi trs = new TrAbsensi();
                trs.setId(rs.getInt("id"));
                trs.setNik(rs.getString("nik"));
                trs.setBranchId(rs.getShort("branch_id"));
                trs.setIdabsensi(rs.getString("idabsensi"));

                Calendar cal1 = null;
                if (rs.getDate("jammasuk") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("jammasuk").getTime());
                }
                trs.setJamMasuk(cal1);

                Calendar cal2 = null;
                if (rs.getDate("jamkeluar") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getTimestamp("jamkeluar").getTime());
                }
                trs.setJamKeluar(cal2);
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrAbsensi(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrRequest(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        //        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_tr_request = clientConn.prepareCall(ClientProcedures.get_tr_request);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_tr_request.executeQuery();
            List<TrRequest> list = new ArrayList<TrRequest>();

            while (rs.next()) {
                TrRequest tr = new TrRequest();
                tr.setBranchId(rs.getShort("branch_id"));
                tr.setId(rs.getInt("id"));
                tr.setNoaju(rs.getString("noaju"));
                tr.setGeraiSrc(rs.getShort("gerai"));
                tr.setNik(rs.getString("nik"));

                Calendar cal1 = null;
                if (rs.getDate("mulai") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("mulai").getTime());
                }
                tr.setMulai(cal1);

                Calendar cal2 = null;
                if (rs.getDate("akhir") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getTimestamp("akhir").getTime());
                }
                tr.setAkhir(cal2);

                tr.setAlasan(rs.getString("alasan"));
                tr.setPost(rs.getString("post"));
                tr.setGeraiDest(rs.getShort("geraitujuan"));
                tr.setPosisi(rs.getString("posisi"));
                tr.setSpv(rs.getString("spv"));

                list.add(tr);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                serverConn = ServerTrsService.instance().putTrRequest(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT>");
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsMitraPaymentList(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement call_get_trs_mitra_peyment_mst = clientConn.prepareCall(ClientProcedures.get_trs_mitra_payment_mst);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_trs_mitra_peyment_mst.executeQuery();
            List<TrsMitraPaymentMst> list = new ArrayList<TrsMitraPaymentMst>();
            while (rs.next()) {
                TrsMitraPaymentMst trs = new TrsMitraPaymentMst();
                trs.setPaymentId(rs.getInt("payment_id"));
                trs.setMitraId(rs.getInt("mitra_id"));
                trs.setTransNum(rs.getString("trans_num"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
                }
                trs.setTransDate(cal1);

                trs.setSubtotal(rs.getFloat("subtotal"));
                trs.setDiscount(rs.getFloat("discount"));
                trs.setTax(rs.getFloat("tax"));
                trs.setTotal(rs.getFloat("total"));
                trs.setNotes(rs.getString("notes"));
                trs.setRekapId(rs.getInt("rekap_id"));
                trs.setStatusId(rs.getInt("status_id"));
                trs.setPrinted(rs.getString("printed"));
                trs.setInsertLog(rs.getString("insert_log"));
                trs.setEditLog(rs.getString("edit_log"));
                trs.setBranchId(rs.getInt("branch_id"));
                trs.setRelationId(rs.getInt("relation_id"));
                
                LOG.debug("[GET RELATION_ID] : " + rs.getInt("relation_id"));
                
                List<TrsMitraPaymentDetail> detail = getTrsMitraPaymentDetails(header, trs.getPaymentId(), clientConn);
                trs.setDetails(detail);

                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putTrsMitraPaymentList(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private List<TrsMitraPaymentDetail> getTrsMitraPaymentDetails(Header header, int paymentId, Connection clientConn) throws SQLException {

        CallableStatement call_get_trs_mitra_peyment_detail = clientConn.prepareCall(ClientProcedures.get_trs_mitra_payment_detail);
        call_get_trs_mitra_peyment_detail.setInt("payment_id", paymentId);
        ResultSet rs = call_get_trs_mitra_peyment_detail.executeQuery();
        List<TrsMitraPaymentDetail> list = new ArrayList<TrsMitraPaymentDetail>();
        while (rs.next()) {
            TrsMitraPaymentDetail trs = new TrsMitraPaymentDetail();
            trs.setPaymentDetailId(rs.getInt("payment_detail_id"));
            trs.setPaymentId(rs.getInt("payment_id"));
            trs.setItemId(rs.getInt("item_id"));
            trs.setQuantity(rs.getFloat("quantity"));
            trs.setQuantity2(rs.getFloat("quantity2"));
            trs.setPrice(rs.getFloat("price"));
            trs.setDisc(rs.getFloat("disc"));
            trs.setTax(rs.getFloat("tax"));
            trs.setAmount(rs.getInt("amount"));
            trs.setBranchId(rs.getInt("branch_id"));
            list.add(trs);
        }
        
        return  list;

    } 

    private void getMstItemRequest(Header header, Connection clientConn, Connection serverConn) throws SQLException {
        try {
            CallableStatement call_get_mst_item_request = clientConn.prepareCall(ClientProcedures.get_mst_item_request);

            ConnectionManager.instance().isOpen(clientConn);
            ResultSet rs = call_get_mst_item_request.executeQuery();
            List<MstItemRequest> list = new ArrayList<MstItemRequest>();
            while (rs.next()) {
                MstItemRequest trs = new MstItemRequest();
                trs.setItemRequestId(rs.getInt("item_request_id"));
                trs.setMerk(rs.getString("merk"));
                trs.setDeskripsi(rs.getString("deskripsi"));
                trs.setFlavour(rs.getString("flavour"));
                trs.setPack(rs.getString("pack"));
                trs.setSize(rs.getString("size"));
                trs.setInsertLog(rs.getString("insert_log"));
                trs.setEditLog(rs.getString("edit_log"));
                
                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getTimestamp("trans_date").getTime());
                }
                trs.setTransDate(cal1);
                
                trs.setBranchId(rs.getInt("branch_id"));
                
                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ServerTrsService.instance().putMstItemRequest(header, list, serverConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getMstPkmGerai(Header header, Connection clientConn, Connection serverConn) {
        try {
            CallableStatement call_get_mst_pkm_gerai = clientConn.prepareCall(ServerProcedures.get_mst_pkm_gerai);
            //call_get_mst_pkm_gerai.setInt("branch_id", header.getBranchId());
            
            ResultSet rs = call_get_mst_pkm_gerai.executeQuery();
            List<MstPkmGerai> list = new ArrayList<MstPkmGerai>();
            while (rs.next()) {
                MstPkmGerai pkm = new MstPkmGerai();
                pkm.setBranchId(rs.getInt("branch_id"));
                pkm.setItemId(rs.getInt("item_id"));
                pkm.setPkm(rs.getFloat("pkm"));
                pkm.setDsi(rs.getFloat("dsi"));

                list.add(pkm);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                /* Put into mst_item_display */
                ClientMstService.instance().putMstPkmGerai(header, list, clientConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }
    
}
